Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.OleDb
Imports System.Xml

<System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Naming", "CA1724:TypeNamesShouldNotMatchNamespaces")> <DtsConnection(ConnectionType:="EXCEL2VB", _
  DisplayName:="Excel2ConnectionManager (VB)", _
  Description:="Connection manager for Excel files with support for Import Mode", _
  UITypeName:="Excel2ConnMgrUIVB.Excel2ConnMgrUIVB,Excel2ConnMgrUIVB,Version=1.0.0.0,Culture=neutral,PublicKeyToken=<insert public key token here>")> _
Public Class Excel2ConnMgrVB
    Inherits ConnectionManagerBase

#Region " Variables & Constants "

    Private _excelFile As String = String.Empty
    Private Const FIRSTROWHASNAMES_DEFAULT As Boolean = True
    Private _firstRowHasColumnNames As Boolean = FIRSTROWHASNAMES_DEFAULT
    'Private Const USEIMPORTMODE_DEFAULT As Boolean = False
    Private _useImportMode As Boolean
    Private _connectionString As String = String.Empty

    Private Const CONNECTIONSTRING_TEMPLATE As String = _
      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<file>;Extended Properties=""Excel 8.0;<extprop>"""

#End Region

#Region " Constructors "

    Public Sub New()
        UpdateConnectionString()
    End Sub

    Public Sub New(ByVal excelFile As String)
        Me._excelFile = excelFile

        UpdateConnectionString()
    End Sub

    Public Sub New(ByVal excelFile As String, ByVal hdrValue As Boolean, ByVal imexValue As Boolean)
        Me._excelFile = excelFile
        Me._firstRowHasColumnNames = hdrValue
        Me._useImportMode = imexValue

        UpdateConnectionString()
    End Sub

#End Region

#Region " Properties "

    Public Property ExcelFile() As String
        Get
            Return Me._excelFile
        End Get

        Set(ByVal value As String)
            Me._excelFile = value
        End Set
    End Property

    Public Property FirstRowHasColumnNames() As Boolean
        Get
            Return Me._firstRowHasColumnNames
        End Get

        Set(ByVal value As Boolean)
            Me._firstRowHasColumnNames = value
        End Set
    End Property

    Public Property UseImportMode() As Boolean
        Get
            Return Me._useImportMode
        End Get

        Set(ByVal value As Boolean)
            Me._useImportMode = value
        End Set
    End Property

    Public Overrides Property ConnectionString() As String
        Get
            UpdateConnectionString()
            Return Me._connectionString
        End Get

        Set(ByVal value As String)
            Me._connectionString = value
        End Set
    End Property

#End Region

#Region " Methods "

    Public Overrides Function AcquireConnection(ByVal txn As Object) As Object

        Dim excelConnection As New OleDbConnection

        UpdateConnectionString()

        With excelConnection
            .ConnectionString = _connectionString
            .Open()
        End With

        Return excelConnection

    End Function

    Public Overrides Sub ReleaseConnection(ByVal connection As Object)
        ' Nothing to do.
    End Sub

    Public Overrides Function Validate(ByVal infoEvents As Microsoft.SqlServer.Dts.Runtime.IDTSInfoEvents) As Microsoft.SqlServer.Dts.Runtime.DTSExecResult

        If String.IsNullOrEmpty(_excelFile) Then
            infoEvents.FireError(0, "ExcelConnectionManager2", "No Excel file specified", String.Empty, 0)
            Return DTSExecResult.Failure
        Else
            Return DTSExecResult.Success
        End If

    End Function

#End Region

#Region " Helper functions "

    Private Sub UpdateConnectionString()

        Dim temporaryString As String = CONNECTIONSTRING_TEMPLATE
        Dim extendedProperties As String = String.Empty

        ' Fill in Data Source property.
        temporaryString = temporaryString.Replace("<file>", _excelFile)

        ' Fill in extended properties.
        If _firstRowHasColumnNames Then
            extendedProperties &= "HDR=Yes;"
        Else
            extendedProperties &= "HDR=No;"
        End If
        If _useImportMode Then
            extendedProperties &= "IMEX=1;"
        End If
        temporaryString = temporaryString.Replace("<extprop>", extendedProperties)

        _connectionString = temporaryString

    End Sub

    'Private Function GetExcelFileFromConnectionString(ByVal currentString As String) As String

    '  Const SEARCH_NAME As String = "data source="
    '  Dim localString As String
    '  Dim fileIndex As Integer
    '  Dim fileLength As Integer
    '  Dim fileValue As String

    '  localString = currentString.ToLower
    '  fileIndex = localString.IndexOf(SEARCH_NAME) + SEARCH_NAME.Length
    '  If fileIndex > 1 Then
    '    fileLength = localString.IndexOf(";", fileIndex)
    '    fileValue = currentString.Substring(fileIndex, fileLength)
    '    Return fileValue
    '  Else
    '    Return String.Empty
    '  End If

    'End Function

    'Private Function GetHdrFromConnectionString(ByVal currentString As String) As Boolean

    '  Const SEARCH_NAME As String = "hdr="
    '  Dim localString As String
    '  Dim hdrIndex As Integer
    '  Dim hdrValue As String

    '  localString = currentString.ToLower
    '  hdrIndex = localString.IndexOf(SEARCH_NAME) + SEARCH_NAME.Length
    '  If hdrIndex > -1 Then
    '    hdrValue = localString.Substring(hdrIndex, 1)
    '    If hdrValue = "y" Then
    '      Return True
    '    Else
    '      Return False
    '    End If
    '  Else
    '    Return FIRSTROWHASNAMES_DEFAULT
    '  End If

    'End Function

    'Private Function GetImexFromConnectionString(ByVal currentString As String) As Boolean

    '  Const SEARCH_NAME As String = "imex="
    '  Dim localString As String
    '  Dim imexIndex As Integer
    '  Dim imexValue As String

    '  localString = currentString.ToLower
    '  imexIndex = localString.IndexOf(SEARCH_NAME) + SEARCH_NAME.Length
    '  If imexIndex > -1 Then
    '    imexValue = localString.Substring(imexIndex, 1)
    '    If imexValue = "1" Then
    '      Return True
    '    Else
    '      Return False
    '    End If
    '  Else
    '    Return USEIMPORTMODE_DEFAULT
    '  End If

    'End Function

#End Region
End Class
